﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Data;
using System.Data.Common;

namespace QLHPHP
{
    /// <summary>
    /// Class ve mon hoc
    /// </summary>
    public static class MonHoc
    {
        /// <summary>
        /// Lay danh sach mon hoc
        /// </summary>
        public static DataTable LayDSMonHoc()
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetSubjects";
            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay danh sach HKNH theo MaMonHoc
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LayHKNHTheoMaMonHoc(string MaMonHoc)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetHKNKByIdSubject";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@mamonhoc";
            param.DbType = DbType.String;
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay danh sach mon hoc tich luy theo mssv
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LayMHTichLuyTuMSSV(int MSSV)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetMonHocTichLuyByMSSV";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MSSV";
            param.DbType = DbType.String;
            param.Value = MSSV;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay danh sach MonHoc theo HKNH
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LayDSMonHocTheoHKNH(string HocKy)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetSubjectByHKNH";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@hocky";
            param.DbType = DbType.String;
            param.Value = HocKy;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay danh sach MonHoc theo Nganh
        /// </summary>
        public static DataTable LayDSMonHocTheoNganh(string MaNganh)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetSubjectsByNganh";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MaNganh";
            param.DbType = DbType.String;
            param.Value = MaNganh;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay danh sach MonHoc theo SoPhieuDK
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LayDSMonHocTheoSoPhieuDK(int SoPhieuDK)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetSubjectBySoPhieuDK";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@SoPhieuDK";
            param.DbType = DbType.Int16;
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);  
        }

        /// <summary>
        /// Lay thong tin chi tiet dang ky theo SoPhieuDK
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LayThongTinDKChiTietTheoSoPhieuDK(int SoPhieuDK)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetInfoDKBySoPhieuDK";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@SoPhieuDK";
            param.DbType = DbType.Int16;
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay tong tin chi trong phieu dk
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable TongTCDKTheoSoPhieuDK(int SoPhieuDK)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "SUMTCDKBySoPhieuDK";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@SoPhieuDK";
            param.DbType = DbType.Int16;
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay tong tin chi trong mon hoc tich luy
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable TongTCTuMonHocTichLuy(int MSSV)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetTongTinChiTichLuyByMSSV";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MSSV";
            param.DbType = DbType.Int32;
            param.Value = MSSV;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);  
        }

        /// <summary>
        /// Lay so phieu tu mssv
        /// </summary>
        /// <param name="MaKhoa"></param>
        /// <returns></returns>
        public static DataTable LaySoPhieuDKTuMSSV(int MSSV)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetSoPhieuDKByMSSV";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MSSV";
            param.DbType = DbType.Int32;
            param.Value = MSSV;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Them Phieu dk
        /// </summary>
        /// <returns></returns>
        public static bool ThemPhieuDK(int SoPhieuDK,int MSSV,DateTime NgayDK)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "CreatePhieuDK";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@MSSV";
            param.Value = MSSV;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.DateTime;
            param.ParameterName = "@NgayDK";
            param.Value = NgayDK;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Them tien da dong vao Phieu dk
        /// </summary>
        /// <returns></returns>
        public static bool ThemHocPhiVaoPhieuDK(int SoPhieuDK,int SoTienDaDong)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "AddMoneyInPhieuDK";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoTienDaDong";
            param.Value = SoTienDaDong;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Them tien phai dong vao phieu dk
        /// </summary>
        /// <returns></returns>
        public static bool ThemTienPhaiDongVaoPhieuDK(int SoPhieuDK, int SoTienPhaiDong,int SoTienDaDong)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "AddMoneyPhaiDongInSoPhieuDK";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoTienPhaiDong";
            param.Value = SoTienPhaiDong;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoTienDaDong";
            param.Value = SoTienDaDong;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Them tien da dong vao Phieu dk
        /// </summary>
        /// <returns></returns>
        public static bool ThemHPDaDongVaoPhieuDK(int SoPhieuDK, int SoTienDaDong)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "AddMoneyDaDongInSPDK";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoTienDaDong";
            param.Value = SoTienDaDong;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Them Phieu dk chi tiet
        /// </summary>
        /// <returns></returns>
        public static bool ThemPhieuDKChiTiet(int SoPhieuDK,string MaMonHoc)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "CreateCTPhieuDK";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaMonHoc";
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Them mon hoc tich luy
        /// </summary>
        /// <returns></returns>
        public static bool ThemMonHocTichLuy(int MSSV, string MaMonHoc, int SoPhieuDK)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "CreateMHTichLuy";

            DbParameter param = comm.CreateParameter();
            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@MSSV";
            param.Value = MSSV;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaMonHoc";
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@SoPhieuDK";
            param.Value = SoPhieuDK;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Lay so phieu dk lơn nhat
        /// </summary>
        /// <returns></returns>
        public static DataTable LaySPMax()
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetNumMaxDK";
            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay so tien/1 tinh chi tu bang tham so
        /// </summary>
        /// <returns></returns>
        public static DataTable LayGTTC()
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetGiaTriTC";
            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Them mon hoc
        /// </summary>
        public static bool ThemMonHoc(string MaMonHoc, string TenMonHoc, int MaLoaiMon, int SoDVHT, int TCLT, int TCTH)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "CreateSubject";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MaMonHoc";
            param.DbType = DbType.String;
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TenMonHoc";
            param.DbType = DbType.String;
            param.Value = TenMonHoc;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MaLoaiMon";
            param.DbType = DbType.Int32;
            param.Value = MaLoaiMon;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@SoDVHT";
            param.DbType = DbType.Int32;
            param.Value = SoDVHT;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TCLT";
            param.DbType = DbType.Int32;
            param.Value = TCLT;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TCTH";
            param.DbType = DbType.Int32;
            param.Value = TCTH;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Xoa mon hoc
        /// </summary>
        public static bool XoaMonHoc(string MaMonHoc)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "DeleteSubject";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MaMonHoc";
            param.DbType = DbType.String;
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Cap nhap mon hoc
        /// </summary>
        public static bool CapNhapMonHoc(string MaMonHoc, string TenMonHoc, int MaLoaiMon, int SoDVHT, int TCLT, int TCTH)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "UpdateSubject";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MaMonHoc";
            param.DbType = DbType.String;
            param.Value = MaMonHoc;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TenMonHoc";
            param.DbType = DbType.String;
            param.Value = TenMonHoc;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MaLoaiMon";
            param.DbType = DbType.Int32;
            param.Value = MaLoaiMon;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@SoDVHT";
            param.DbType = DbType.Int32;
            param.Value = SoDVHT;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TCLT";
            param.DbType = DbType.Int32;
            param.Value = TCLT;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TCTH";
            param.DbType = DbType.Int32;
            param.Value = TCTH;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

    }
}
